package com.sdyeda.readiot.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;

import com.sdyeda.readiot.data.SetData;
import com.sdyeda.readiot.data.SetSave;

public class PoiTools {
	// 工作表名称
	private static final String SHEET1 = "Setting";
	private static final String SHEET2 = "Iotinfo";
	// 表头
	private static final String KEY = "关键字";
	private static final String VALUE = "值";
	private static final String TYPEID = "类型编码";
	private static final String TYPENAME = "类型名称";
	private static final String JARPATH = "接口文件";
	private static final String CLASSNAME ="类名";
	private static final String WRITESQL = "写数据语句";
	private static final String READSQL = "读取最新数据语句";

	static public Boolean ExportSetting(File file, Map<String, SetData> sets, List<SetSave> sets2) {

		try (HSSFWorkbook workbook = new HSSFWorkbook()) {
			HSSFSheet sheet = workbook.createSheet(SHEET1);
			sheet.setColumnWidth(0, 20 * 256);
			sheet.setColumnWidth(1, 50 * 256);

			HSSFCellStyle style = workbook.createCellStyle();
			style.setWrapText(true);

			// 表头
			HSSFRow head = sheet.createRow(0);
			head.createCell(0).setCellValue(KEY);
			head.createCell(1).setCellValue(VALUE);
			head.getCell(1).setCellStyle(style);

			int line = 1;
			for (SetData set : sets.values()) {
				HSSFRow row = sheet.createRow(line);
				row.createCell(0).setCellValue(set.getName());
				row.createCell(1).setCellValue(set.getValue());
				row.getCell(1).setCellStyle(style);
				line++;
			}

			HSSFSheet sheet2 = workbook.createSheet(SHEET2);
			sheet2.setColumnWidth(0, 12 * 256);
			sheet2.setColumnWidth(1, 12 * 256);
			sheet2.setColumnWidth(2, 50 * 256);
			sheet2.setColumnWidth(3, 50 * 256);
			sheet2.setColumnWidth(4, 50 * 256);
			sheet2.setColumnWidth(5, 50 * 256);
			HSSFRow head2 = sheet2.createRow(0);
			head2.createCell(0).setCellValue(TYPEID);
			head2.createCell(1).setCellValue(TYPENAME);
			head2.createCell(2).setCellValue(JARPATH);
			head2.createCell(3).setCellValue(CLASSNAME);
			head2.createCell(4).setCellValue(WRITESQL);
			head2.createCell(5).setCellValue(READSQL);

			for (int i = 0; i < sets2.size(); i++) {
				HSSFRow row = sheet2.createRow(i + 1);
				row.createCell(0).setCellValue(sets2.get(i).getIottype());
				row.createCell(1).setCellValue(sets2.get(i).getTypename());
				row.createCell(2).setCellValue(sets2.get(i).getJarname());
				row.createCell(3).setCellValue(sets2.get(i).getClassname());
				row.createCell(4).setCellValue(sets2.get(i).getWritedata());
				row.createCell(5).setCellValue(sets2.get(i).getReaddata());
				row.getCell(4).setCellStyle(style);
				row.getCell(5).setCellStyle(style);
			}

			workbook.setActiveSheet(0);

			try {
				FileOutputStream fos = new FileOutputStream(file);
				workbook.write(fos);
				fos.close();
				//System.out.println("write ok!");
				return true;
			} catch (IOException e) {
				//System.out.println(e.toString());
				return false;
			}
		} catch (IOException e) {
			return false;
		}

	}

	@SuppressWarnings("resource")
	public static Map<String, Object> ImportSetting(File file, Map<String, SetData> sets) {
		Map<String, Object> result = new HashMap<String, Object>();
		result.put("success", false);
		result.put("info", "");

		HSSFWorkbook workbook;
		try {
			FileInputStream fis = new FileInputStream(file);
			workbook = new HSSFWorkbook(fis);
			fis.close();
		} catch (Exception e) {
			result.put("info", "打开文件失败！");
			return result;
		}

		// 分析Setting工作表
		try {
			HSSFSheet sheet = workbook.getSheet("Setting");
			System.out.println(sheet);
			if (sheet == null) {
				result.put("info", "文件中缺少工作表" + SHEET1);
				return result;
			}
			HSSFRow head = sheet.getRow(0);
			if (!head.getCell(0).getStringCellValue().equals(KEY)
					|| !head.getCell(1).getStringCellValue().equals(VALUE)) {
				result.put("info", String.format("工作表%s表头格式错误!", SHEET1));
				return result;
			}
			int n = sheet.getPhysicalNumberOfRows();
			List<SetData> newsets = new ArrayList<SetData>();
			for (int i = 1; i < n; i++) {
				HSSFRow row = sheet.getRow(i);
				// System.out.println(row.getCell(0).getStringCellValue() + ":" +
				// row.getCell(1).getStringCellValue());
				
				SetData set = sets.get(row.getCell(0).getStringCellValue());
				if (set != null) {
					// System.out.println("find:" + set.getName());
					if(row.getCell(1).getCellType() == CellType.NUMERIC) 
						set.setValue(Long.toString((long)row.getCell(1).getNumericCellValue()));
					else
						set.setValue(row.getCell(1).getStringCellValue());
					newsets.add(set);
				}
			}

			result.put("sets", newsets);

			// 分析Setting工作表false) 
			HSSFSheet sheet2 = workbook.getSheet(SHEET2);

			if (sheet2 == null) {
				result.put("info", "文件中缺少工作表" + SHEET2);
				return result;
			}

			HSSFRow head2 = sheet2.getRow(0);
			if (!head2.getCell(0).getStringCellValue().equals(TYPEID)
					|| !head2.getCell(1).getStringCellValue().equals(TYPENAME)
					|| !head2.getCell(2).getStringCellValue().equals(JARPATH)
					|| !head2.getCell(3).getStringCellValue().equals(CLASSNAME) 
					|| !head2.getCell(4).getStringCellValue().equals(WRITESQL)
					|| !head2.getCell(5).getStringCellValue().equals(READSQL)) {
				result.put("info", String.format("工作表%s表头格式错误!", SHEET2));
				return result;
			}

			n = sheet2.getPhysicalNumberOfRows();
			List<SetSave> iots = new ArrayList<SetSave>();
			for (int i = 1; i < n; i++) {
				HSSFRow row = sheet2.getRow(i);
				SetSave iot = new SetSave();
				
				//容易变成数字
				if(row.getCell(0).getCellType()==CellType.NUMERIC)
					iot.setIottype(Long.toString((long)row.getCell(0).getNumericCellValue()));	
				else
					iot.setIottype(row.getCell(0).getStringCellValue());	
				
				iot.setTypename(row.getCell(1).getStringCellValue());
				iot.setJarname(row.getCell(2).getStringCellValue());
				iot.setClassname(row.getCell(3).getStringCellValue());
				iot.setWritedata(row.getCell(4).getStringCellValue());
				iot.setReaddata(row.getCell(5).getStringCellValue());
				iots.add(iot);
			}
			//System.out.println(iots.toString());
			result.put("iots", iots);
			result.put("info", "导入成功！");
			result.put("success", true);

			return result;
		} catch (Exception e) {
			result.put("success", false);
			result.put("info", "导入失败！\n"+e.toString());
			return result;
		}
	}

}
